import math


def generate_student_user_auth_group():
    name_list = []
    d = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    with open("in/names.txt",'r',encoding="utf8") as f:
        line = f.readline()
        while line:
            name_list.append(line.strip())
            line = f.readline()
    user_list = []
    auth_list = []
    student_list = []
    group_list = []
    allocation_list = []
    uid = 1
    for class_id in range(1,201):
        for gender in range(2):
            for i in range(25):
                user = {}
                student = {}
                auth = {}
                user["uid"] = uid
                user["name"] = name_list[uid]
                user["gender"] = gender
                student["uid"] = user["uid"]
                student["studentid"] = str(250121*10000+uid)
                student["class_id"] = class_id
                auth["username"] = student["studentid"]
                auth["password"] = student["studentid"]
                auth["uid"] = user["uid"]
                user_list.append(user)
                auth_list.append(auth)
                student_list.append(student)
                if i+1 < 16:
                    q = int(math.sqrt(i+1))
                    allocation = {}
                    allocation["uid"] = user["uid"]
                    allocation["is_creator"] = 0
                    allocation["group_id"] = class_id*100+gender*10+q
                    if q*q==i+1:
                        group = {}
                        group["id"] = allocation["group_id"]
                        group["name"] = f"{user['name']}的队伍"
                        group["invite_code"] = d[class_id//10]+d[class_id%10]+d[gender]+d[q]
                        group["describe"] = f"{user['name']}创建的队伍"
                        group_list.append(group)
                        allocation["is_creator"] = 1
                    allocation_list.append(allocation)
                uid += 1
    write_user_sql(user_list)
    write_student_sql(student_list)
    write_auth_sql(auth_list)
    write_group_sql(group_list)
    write_group_allocation_sql(allocation_list)

def generate_build_room_bed():
    builing_list = []
    room_list = []
    bed_list = []
    allocation = []
    #共有20栋楼
    for i in range(20):
        builing = {}
        builing["id"] = i+1
        builing["name"] = f"{i+1}号楼"
        builing["describe"] = f"这是{i+1}号楼"
        #每楼6层
        for j in range(6):
            #每层20个房间，分别分给20个班
            for k in range(20):
                index = i*6*20+j*20+k
                room = {}
                room["id"] = builing["id"]*1000 +  (j+1)*100 + k + 1
                room["status"] = 8 #空床位
                room["order_num"] = index%200 + 1 #分配的班号
                room["building_id"] = builing["id"]
                room["gender"] = (index//200)%2
                a = {}
                a["class_id"] = index%200 + 1
                a["room_id"] = room["id"]
                allocation.append(a)
                #每间8床
                for b in range(8):
                    bed = {}
                    bed["id"] = room["id"]*10+b+1
                    bed["room_id"] = room["id"]
                    bed["name"] = f"{b+1}号床"
                    bed_list.append(bed)
                room_list.append(room)
        builing_list.append(builing)
        write_building_sql(builing_list)
        write_room_sql(room_list)
        write_bed_sql(bed_list)
        write_allocation_sql(allocation)

def write_building_sql(builing_list):
    with open("out/buildings.sql","w",encoding="utf8") as f:
        for b in builing_list:
            statement = f'INSERT INTO buildings (id,name,`describe`) VALUE({b["id"]},\"{b["name"]}\",\"{b["describe"]}\");\n'
            f.write(statement)

def write_room_sql(room_list):
    with open("out/rooms.sql","w",encoding="utf8") as f:
        for r in room_list:
            statement = f"INSERT INTO rooms (id,status,order_num,building_id,gender) VALUE ({r['id']},{r['status']},{r['order_num']},{r['building_id']},{r['gender']});\n"
            f.write(statement)

def write_bed_sql(bed_list):
    with open("out/beds.sql","w",encoding="utf8") as f:
        for b in bed_list:
            statement = f"INSERT INTO beds (id,room_id,name) VALUE ({b['id']},{b['room_id']},\"{b['name']}\");\n"
            f.write(statement)

def write_allocation_sql(allocation_list):
    with open("out/class_room.sql","w",encoding="utf8") as f:
        for a in allocation_list:
            statement = f"INSERT INTO class_room (class_id,room_id) VALUE ({a['class_id']},{a['room_id']});\n"
            f.write(statement)

def write_user_sql(user_list):
    with open("out/users.sql", "w", encoding="utf8") as f:
        for user in user_list:
            statement = f"INSERT INTO users (uid,name,gender) VALUE ({user['uid']},\"{user['name']}\",{user['gender']});\n"
            f.write(statement)

def write_student_sql(student_list):
    with open("out/student_info.sql", "w", encoding="utf8") as f:
        for s in student_list:
            statement = f"INSERT INTO student_info (uid,studentid,class_id) VALUE ({s['uid']},\"{s['studentid']}\",{s['class_id']});\n"
            f.write(statement)

def write_auth_sql(auth_list):
    with open("out/auth.sql","w",encoding="utf8") as f:
        for a in auth_list:
            statement = f"INSERT INTO auth (username,password,uid) VALUE (\"{a['username']}\",\"{a['password']}\",{a['uid']});\n"
            f.write(statement)

def write_group_sql(group_list):
    with open("out/groups.sql","w",encoding="utf8") as f:
        for g in group_list:
            statement = f"INSERT INTO `groups` (id,name,invite_code,`describe`) VALUE ({g['id']},\"{g['name']}\",\"{g['invite_code']}\",\"{g['describe']}\");\n"
            f.write(statement)

def write_group_allocation_sql(allocation_list):
    with open("out/groups_user.sql","w",encoding="utf8") as f:
        for a in allocation_list:
            statement = f"INSERT INTO groups_user(uid,group_id,is_creator) VALUE ({a['uid']},{a['group_id']},{a['is_creator']});\n"
            f.write(statement)

def generate_class():
    with open("out/class.sql","w",encoding="utf8") as f:
        for i in range(200):
            id = i + 1
            name = f"25级{id}班"
            statement = f"INSERT INTO class (id,name) VALUE ({id},\"{name}\");\n"
            f.write(statement)



if __name__ == "__main__":
    generate_build_room_bed()